import sys
import os
import logging
import re
import json
from typing import Dict, List, Any, Optional, Tuple

# 添加项目根路径到 sys.path
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..'))

# 导入 MySQLUtil
from shared.utils.MySQLUtil import MySQLUtil

# 设置日志
logger = logging.getLogger(__name__)

def get_用人单位对毕业生各项能力素质的满意情况分布(
    project_id: int,
    questionnaire_ids: List[int],
    product_code: Optional[str] = None,
    project_code: Optional[str] = None,
    region_code: Optional[str] = None
) -> Dict[str, Any]:
    """
    用人单位对毕业生各项能力素质的满意情况分布 - 指标计算函数
    
    ## 指标说明
    该指标用于统计用人单位对毕业生各项能力素质的满意度情况。
    计算逻辑：对每项能力素质(政治觉悟、职业道德等)，计算选择前3项(很满意、满意、基本满意)占前5项(很满意、满意、基本满意、不满意、很不满意)的比例。
    
    ## Args
        project_id (int): 项目ID，用于查询项目配置信息
        questionnaire_ids (List[int]): 问卷ID集合，用于确定数据范围
        product_code (Optional[str]): 产品编码，默认为PD0018
        project_code (Optional[str]): 项目编码
        region_code (Optional[str]): 区域编码
        
    ## 示例
    ### 输入
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159]
    }
    ```
    
    ### 输出
    ```json
    {
        "success": true,
        "message": "ok", 
        "code": 0,
        "result": [
            {
                "subIndicator": "政治觉悟",
                "ratio": 0.85
            },
            {
                "subIndicator": "职业道德",
                "ratio": 0.82
            }
        ]
    }
    ```
    """
    logger.info(f"开始计算指标: 用人单位对毕业生各项能力素质的满意情况分布, 项目ID: {project_id}")
    
    try:
        db = MySQLUtil()  

        # 1. 查询项目配置信息
        project_sql = """
        SELECT client_code, item_year, dy_target_items, split_tb_paper 
        FROM client_item 
        WHERE id = %s
        """
        project_info = db.fetchone(project_sql, (project_id,))
        if not project_info:
            raise ValueError(f"未找到项目ID={project_id}的配置信息")

        client_code = project_info['client_code']
        item_year = project_info['item_year']
        split_tb_paper = project_info['split_tb_paper']
        
        logger.info(f"项目配置: client_code={client_code}, item_year={item_year}, split_tb_paper={split_tb_paper}")

        # 2. 查询问卷信息
        questionnaire_sql = f"""
        SELECT id, dy_target 
        FROM wt_template_customer 
        WHERE id IN ({','.join(['%s'] * len(questionnaire_ids))})
        """
        questionnaires = db.fetchall(questionnaire_sql, tuple(questionnaire_ids))
        if not questionnaires:
            raise ValueError(f"未找到问卷ID集合={questionnaire_ids}的配置信息")
        
        logger.info(f"查询到问卷信息: {questionnaires}")

        # 3. 过滤EMPLOYER调研对象的问卷
        valid_questionnaire_ids = [q['id'] for q in questionnaires if q['dy_target'] == 'EMPLOYER']
        if not valid_questionnaire_ids:
            raise ValueError("未找到EMPLOYER调研对象的问卷ID")
            
        logger.info(f"找到EMPLOYER问卷ID: {valid_questionnaire_ids}")

        # 4. 定义问题编码和子指标映射
        question_mapping = {
            'T00024885': '政治觉悟',
            'T00003327': '职业道德',
            'T00024886': '职业素养',
            'T00000454': '专业水平',
            'T00000455': '职业能力',
            'T00024887': '学习能力',
            'T00002309': '创新能力'
        }

        # 5. 查询问题信息
        question_sql = f"""
        SELECT id, wt_code, wt_obj 
        FROM wt_template_question_customer 
        WHERE cd_template_id = %s AND wt_code IN ({','.join(['%s'] * len(question_mapping))}) AND is_del = 0
        """
        questions = db.fetchall(question_sql, (valid_questionnaire_ids[0], *question_mapping.keys()))
        if not questions:
            raise ValueError("未找到指定问题编码的问题信息")
            
        logger.info(f"找到问题信息: 共{len(questions)}个问题")

        # 6. 构建问题ID映射
        question_id_map = {q['wt_code']: q['id'] for q in questions}

        # 7. 构建动态表名
        answer_table = f"re_dy_paper_answer_{split_tb_paper}"

        # 8. 计算每个子指标的占比
        result = []
        for wt_code, sub_indicator in question_mapping.items():
            if wt_code not in question_id_map:
                logger.warning(f"问题编码 {wt_code} 未找到对应的问题ID")
                continue
                
            wid = question_id_map[wt_code]
            ratio_sql = f"""
            SELECT
            (sum(c1)+sum(c2)+sum(c3))/(sum(c1)+sum(c2)+sum(c3)+sum(c4)+sum(c5)) as ratio_sum
            FROM {answer_table}
            WHERE cd_template_id = %s AND wid = %s AND ans_true = 1
            """
            ratio_data = db.fetchone(ratio_sql, (valid_questionnaire_ids[0], wid))
            
            if not ratio_data or ratio_data['ratio_sum'] is None:
                logger.warning(f"子指标 {sub_indicator} 无有效数据")
                ratio = 0.0
            else:
                ratio = float(ratio_data['ratio_sum'])
                
            result.append({
                "subIndicator": sub_indicator,
                "ratio": round(ratio, 4)
            })

        logger.info(f"指标 '用人单位对毕业生各项能力素质的满意情况分布' 计算成功")
        return {
            "success": True,
            "message": "ok",
            "code": 0,
            "result": result
        }

    except Exception as e:
        logger.error(f"计算指标 '用人单位对毕业生各项能力素质的满意情况分布' 时发生错误: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"数据获取失败: 用人单位对毕业生各项能力素质的满意情况分布",
            "code": 500,
            "error": str(e)
        }